You will need to store data in different tables
in order to create a scalable and
efficient solution for some business situations. Trying to store everything
in a single table would likely be inefficient
and difficult to work with. How does this work in Dataverse? Imagine that you need to create a system to manage sales orders. You will need a product list
along with the inventory, the cost, as well as the
selling price of the item. You also need a master list of your customers with their
addresses and credit ratings. Finally, you will need to
manage invoices for sales, so you will want a way
to store invoice data. Remember that each
of these items also contain their
own information. So trying to create a
single table to support these items and functionality
will be unproductive. A better way to approach your system is to
create four tables. One for the customers, another one for products, one for invoices, and one for line items. Creating a table for each of these items and relating them to one another will allow you to build an efficient solution. This will ensure that
your solution can scale while maintaining
a high performance. Splitting your data into multiple tables also means
that you will not have to store repetitive data or support huge rows with large
amounts of blank data. Additionally,
reporting will be much easier if you split the
data into separate tables. How does these tables
relate to one another? Relationships between
tables exist in many forms, but the first is one-to-many. This is also known as parent
to child relationships. In your invoice
table, for example, the invoice table is the parent and the line item is the child. Typically, the child rows will not exist
without a parent row. Now, let's break this
down into more detail. A column that only
allows unique values, such as an invoice number is used to identify the parent row. This unique column
is called a key. The same value or the parent key is stored
in the related child rows, this column is called
a foreign key when the child row is used to
store the parent key value. Filtering is used to
display child rows with a value in the foreign key that matches the key value
in the parent row. Again, these child
rows belong to a particular parent row which would be the invoice
in this example. Microsoft Dataverse
also supports a many-to-many
relationship where multiple rows in a
table are linked to multiple rows
in another table. Now you know that splitting
data into different tables helps you to create an efficient solution
design that can scale. It is also great to know
that Microsoft Dataverse already contains many of the
tables that you will need.